﻿using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace windows_mssql_exporter.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class MetricsController : ControllerBase
    {
        private readonly ILogger<MetricsController> _logger;

        public MetricsController(ILogger<MetricsController> logger)
        {
            _logger = logger;
        }

        [HttpGet]
        public string Get()
        {
            SqlConnection sqlConnection = new SqlConnection(AppConfig.Current.DataSource);
            SqlCommand sqlCommand = new SqlCommand("",sqlConnection);
            StringBuilder stringBuilder = new StringBuilder();
            try
            {
                sqlConnection.Open();
                
                stringBuilder.Append("# UP Status\n");
                sqlCommand.CommandText = "select 1";
                stringBuilder.Append($"mssql_up {sqlCommand.ExecuteScalar()}\n");

                stringBuilder.Append("# Instance version\n");
                sqlCommand.CommandText = @"SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion";
                stringBuilder.Append($"mssql_product_version {sqlCommand.ExecuteScalar().ToString().Substring(0,2)}\n");

                stringBuilder.Append("# Number of seconds since epoch on local instance\n");
                sqlCommand.CommandText = @"SELECT DATEDIFF(second, '19700101', GETUTCDATE())";
                stringBuilder.Append($"mssql_instance_local_time {sqlCommand.ExecuteScalar()}\n");

                stringBuilder.Append("# Number of active connections\n");
                sqlCommand.CommandText = @"SELECT DB_NAME(sP.dbid), COUNT(sP.spid) FROM sys.sysprocesses sP GROUP BY DB_NAME(sP.dbid)";
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        stringBuilder.Append($"mssql_connections{{database=\"{reader.GetString(0)}\",state=\"current\"}} {reader.GetInt32(1)}\n");
                    }
                }

                stringBuilder.Append("# Number of active client connections\n");
                sqlCommand.CommandText = @"SELECT host_name, DB_NAME(dbid) dbname, COUNT(*) session_count FROM sys.dm_exec_sessions a LEFT JOIN sysprocesses b on a.session_id=b.spid WHERE is_user_process=1 GROUP BY host_name, dbid";
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        stringBuilder.Append($"mssql_client_connections{{client=\"{reader.GetString(0)}\",database=\"{reader.GetString(1)}\"}} {reader.GetInt32(2)}\n");
                    }
                }

                stringBuilder.Append("# Number of user errors/sec since last restart\n");
                sqlCommand.CommandText = @"SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Errors/sec' AND instance_name = 'User Errors'";
                stringBuilder.Append($"mssql_user_errors {sqlCommand.ExecuteScalar()}\n");

                stringBuilder.Append("# Number of kill connection errors/sec since last restart\n");
                sqlCommand.CommandText = @"SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Errors/sec' AND instance_name = 'Kill Connection Errors'";
                stringBuilder.Append($"mssql_kill_connection_errors {sqlCommand.ExecuteScalar()}\n");

                stringBuilder.Append("# Databases states: 0=ONLINE 1=RESTORING 2=RECOVERING 3=RECOVERY_PENDING 4=SUSPECT 5=EMERGENCY 6=OFFLINE 7=COPYING 10=OFFLINE_SECONDARY\n");
                sqlCommand.CommandText = @"SELECT name,state FROM master.sys.databases";
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        stringBuilder.Append($"mssql_database_state{{database=\"{reader.GetString(0)}\"}} {reader.GetByte(1)}\n");
                    }
                }

                stringBuilder.Append("# Total number of times the transaction log for the database has been expanded last restart\n");
                sqlCommand.CommandText = @"SELECT rtrim(instance_name), cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Log Growths' and instance_name <> '_Total'";
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        stringBuilder.Append($"mssql_log_growths{{database=\"{reader.GetString(0)}\"}} {reader.GetInt64(1)}\n");
                    }
                }

                stringBuilder.Append("# Physical sizes of files used by database in KB, their names and types (0=rows, 1=log, 2=filestream,3=n/a 4=fulltext(before v2008 of MSSQL))\n");
                sqlCommand.CommandText = @"SELECT DB_NAME(database_id) AS database_name, name AS logical_name, type, physical_name, (size * CAST(8 AS BIGINT)) size_kb FROM sys.master_files";
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        stringBuilder.Append($"mssql_database_filesize{{database=\"{reader.GetString(0)}\",logicalname=\"{reader.GetString(1)}\",type=\"{reader.GetByte(2)}\",filename=\"{reader.GetString(3)}\"}} {reader.GetInt64(4)}\n");
                    }
                }

                stringBuilder.Append("# mssql_page_read_total=Page reads/sec\n");
                stringBuilder.Append("# mssql_page_write_total=Page writes/sec\n");
                stringBuilder.Append("# mssql_page_life_expectancy=Indicates the minimum number of seconds a page will stay in the buffer pool on this node without references. The traditional advice from Microsoft used to be that the PLE should remain above 300 secondsc\n");
                stringBuilder.Append("# mssql_lazy_write_total=Lazy writes/sec\n");
                stringBuilder.Append("# mssql_page_checkpoint_total=Checkpoint pages/sec\n");
                sqlCommand.CommandText = @"SELECT * FROM 
                                            (
                                                SELECT rtrim(counter_name) as counter_name, cntr_value
                                                FROM sys.dm_os_performance_counters
                                                WHERE counter_name in ('Page reads/sec', 'Page writes/sec', 'Page life expectancy', 'Lazy writes/sec', 'Checkpoint pages/sec')
                                                AND object_name = 'SQLServer:Buffer Manager'
                                            ) d
                                            PIVOT
                                            (
                                            MAX(cntr_value)
                                            FOR counter_name IN ([Page reads/sec], [Page writes/sec], [Page life expectancy], [Lazy writes/sec], [Checkpoint pages/sec])
                                            ) piv";
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        stringBuilder.Append($"mssql_page_read_total {reader.GetInt64(0)}\n");
                        stringBuilder.Append($"mssql_page_write_total {reader.GetInt64(1)}\n");
                        stringBuilder.Append($"mssql_page_life_expectancy {reader.GetInt64(2)}\n");
                        stringBuilder.Append($"mssql_lazy_write_total {reader.GetInt64(3)}\n");
                        stringBuilder.Append($"mssql_page_checkpoint_total {reader.GetInt64(4)}\n");
                    }
                }


                stringBuilder.Append("# mssql_io_stall=Wait time (ms) of stall since last restart\n");
                stringBuilder.Append("# mssql_io_stall_total=Wait time (ms) of stall since last restart\n");
                sqlCommand.CommandText = @"SELECT
                                            cast(DB_Name(a.database_id) as varchar) as name
                                                ,max(io_stall_read_ms)
                                                ,max(io_stall_write_ms)
                                                ,max(io_stall)
                                               -- ,max(io_stall_queued_read_ms),
                                               -- ,max(io_stall_queued_write_ms)
                                            FROM
                                            sys.dm_io_virtual_file_stats(null, null) a
                                            INNER JOIN sys.master_files b ON a.database_id = b.database_id and a.file_id = b.file_id
                                            GROUP BY a.database_id";
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        stringBuilder.Append($"mssql_io_stall{{database=\"{reader.GetString(0)}\",type=\"read\"}} {reader.GetInt64(1)}\n");
                        stringBuilder.Append($"mssql_io_stall{{database=\"{reader.GetString(0)}\",type=\"write\"}} {reader.GetInt64(2)}\n");
                        stringBuilder.Append($"mssql_io_stall_total{{database=\"{reader.GetString(0)}\"}} {reader.GetInt64(3)}\n");
                    }
                }

                stringBuilder.Append("# Number of Transact-SQL command batches received per second. This statistic is affected by all constraints (such as I/O, number of users, cachesize, complexity of requests, and so on). High batch requests mean good throughput\n");
                sqlCommand.CommandText = @"SELECT TOP 1 cntr_value
                                            FROM sys.dm_os_performance_counters 
                                            WHERE counter_name = 'Batch Requests/sec'";
                stringBuilder.Append($"mssql_batch_requests {sqlCommand.ExecuteScalar()}\n");

                stringBuilder.Append("# Number of transactions started for the database per second. Transactions/sec does not count XTP-only transactions (transactions started by a natively compiled stored procedure.)\n");
                sqlCommand.CommandText = @"SELECT rtrim(instance_name), cntr_value
                                            FROM sys.dm_os_performance_counters
                                            WHERE counter_name = 'Transactions/sec' AND instance_name <> '_Total'";
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        stringBuilder.Append($"mssql_transactions{{database=\"{reader.GetString(0)}\"}} {reader.GetInt64(1)}\n");
                    }
                }

                stringBuilder.Append("# mssql_page_fault_count=Number of page faults since last restart\n");
                stringBuilder.Append("# mssql_memory_utilization_percentage=Percentage of memory utilization\n");
                sqlCommand.CommandText = @"SELECT page_fault_count, memory_utilization_percentage FROM sys.dm_os_process_memory";
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        stringBuilder.Append($"mssql_page_fault_count {reader.GetInt64(0)}\n");
                        stringBuilder.Append($"mssql_memory_utilization_percentage {reader.GetInt32(1)}\n");
                    }
                }

                stringBuilder.Append("# mssql_total_physical_memory_kb=Total physical memory in KB\n");
                stringBuilder.Append("# mssql_available_physical_memory_kb=Available physical memory in KB\n");
                stringBuilder.Append("# mssql_total_page_file_kb=Total page file in KB\n");
                stringBuilder.Append("# mssql_available_page_file_kb=Available page file in KB\n");
                sqlCommand.CommandText = @"SELECT total_physical_memory_kb, available_physical_memory_kb, total_page_file_kb, available_page_file_kb FROM sys.dm_os_sys_memory";
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        stringBuilder.Append($"mssql_total_physical_memory_kb {reader.GetInt64(0)}\n");
                        stringBuilder.Append($"mssql_available_physical_memory_kb {reader.GetInt64(1)}\n");
                        stringBuilder.Append($"mssql_total_page_file_kb {reader.GetInt64(2)}\n");
                        stringBuilder.Append($"mssql_available_page_file_kb {reader.GetInt64(3)}\n");
                    }
                }

            }
            catch (Exception err)
            {
                stringBuilder.Append(err.Message);
            }
            finally
            {
                if (sqlConnection.State == System.Data.ConnectionState.Open)
                {
                    sqlConnection.Close();
                }
            }

            return stringBuilder.ToString();
        }
    }
}
